﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.IO;
using System.Configuration;
using System.Collections;
using System.Web;

namespace DAL
{
    public class NguoiDungQuery : DataProvide
    {
        /// <summary>
        /// Lấy họ tên người dùng theo tài khoản
        /// </summary>
        /// <param name="dr">string taikhoan</param>
        /// <returns>taikhoan</returns>
        public NguoiDung_DAL LayNguoiDungTheoTaiKhoan(string taikhoan)
        {
            NguoiDung_DAL nguoiDung = new NguoiDung_DAL();
            SqlConnection chuoiKetNoi = null;
            try
            {
                base.Connect();
                chuoiKetNoi = connection;
                string sqlString = String.Format(@"SELECT * FROM NguoiDung WHERE TaiKhoan = '" + taikhoan + "'");

                SqlDataReader dr = ExecuteReader(sqlString);

                while (dr.Read())
                {
                    nguoiDung = (NguoiDung_DAL)GetDataRowFromDataReader(dr);
                }
            }/*
            catch
            {
                return null;
            }*/
            finally
            {
                if (chuoiKetNoi != null && chuoiKetNoi.State == System.Data.ConnectionState.Open)
                    chuoiKetNoi.Close();
            }
            return nguoiDung;
        }


        public bool KTDangNhap(string taikhoan, string matkhau)
        {
            bool ketQua;
            SqlConnection chuoiKetNoi = null;
            try
            {
                base.Connect();
                chuoiKetNoi = connection;
                string sqlString = String.Format(@"SELECT * FROM NguoiDung WHERE TaiKhoan = '" + taikhoan + "' AND MatKhau = '" + matkhau + "'");

                SqlDataReader dr = ExecuteReader(sqlString);

                ketQua = false;

                if (dr.HasRows == true)
                {
                    ketQua = true;
                }
            }/*
            catch
            {
                return null;
            }*/
            finally
            {
                if (chuoiKetNoi != null && chuoiKetNoi.State == System.Data.ConnectionState.Open)
                    chuoiKetNoi.Close();
            }
            return ketQua;
        }

        public string LayMaQuyenTheoTaiKhoan(string taikhoan)
        {
            string maquyen="";
            SqlConnection chuoiKetNoi = null;
            try
            {
                base.Connect();
                chuoiKetNoi = connection;
                string sqlString = String.Format(@"SELECT MaQuyen FROM NguoiDung WHERE TaiKhoan = '" + taikhoan + "'");

                SqlDataReader dr = ExecuteReader(sqlString);
                while (dr.Read())
                {
                    NguoiDung_DAL nguoiDung = (NguoiDung_DAL)GetDataRowFromDataReader(dr);
                    maquyen = nguoiDung.Maquyen;
                }
            }/*
            catch
            {
                return null;
            }*/
            finally
            {
                if (chuoiKetNoi != null && chuoiKetNoi.State == System.Data.ConnectionState.Open)
                    chuoiKetNoi.Close();
            }
            return maquyen;
        }

        public string LayMatKhauTheoTaiKhoan(string taikhoan)
        {
            string matkhau;
            SqlConnection chuoiKetNoi = null;
            try
            {
                base.Connect();
                chuoiKetNoi = connection;
                string sqlString = String.Format(@"SELECT MatKhau FROM NguoiDung WHERE TaiKhoan = '" + taikhoan + "'");

                matkhau = ExecuteScalar(sqlString).ToString();
            }/*
            catch
            {
                return null;
            }*/
            finally
            {
                if (chuoiKetNoi != null && chuoiKetNoi.State == System.Data.ConnectionState.Open)
                    chuoiKetNoi.Close();
            }
            return matkhau;
            ;
        }

        public bool ThemNguoiDung(NguoiDung_DAL nd)
        {
            bool ketQua = true;
            try
            {
                base.Connect();

                string sqlString = "INSERT INTO NguoiDung(TaiKhoan, MatKhau, MaQuyen, HoTen, DiaChi, Email, SoDienThoai) VALUES(N'" +
                    nd.Taikhoan + "', N'" +
                    nd.Matkhau + "', N'Thành Viên', N'" +
                    nd.Hoten + "', N'" +
                    nd.Diachi + "', N'" +
                    nd.Email + "', N'" +
                    nd.Sodienthoai + "')";

                ExecuteNonQuery(sqlString);
            }/*
            catch (Exception ex)
            {
                ketQua = false;
            }*/
            finally
            {
                if (connection != null && connection.State == System.Data.ConnectionState.Open)
                    Disconnect();
            }
            return ketQua;
        }

        public List<NguoiDung_DAL> LayDanhSachNguoiDungTheoMaQuyen(string maquyen)
        {
            List<NguoiDung_DAL> dsNguoiDung = new List<NguoiDung_DAL>();
            SqlConnection chuoiKetNoi = null;
            try
            {
                base.Connect();
                chuoiKetNoi = connection;
                string sqlString = String.Format(@"SELECT * FROM NGUOIDUNG WHERE MaQuyen = '" + maquyen + "'");

                SqlDataReader dr = ExecuteReader(sqlString);
                while (dr.Read())
                {
                    NguoiDung_DAL nguoiDung = (NguoiDung_DAL)GetDataRowFromDataReader(dr);
                    dsNguoiDung.Add(nguoiDung);
                }
            }/*
            catch
            {
                return null;
            }*/
            finally
            {
                if (chuoiKetNoi != null && chuoiKetNoi.State == System.Data.ConnectionState.Open)
                    chuoiKetNoi.Close();
            }
            return dsNguoiDung;
        }

        public bool XoaNguoiDungTheoTaiKhoan(string taikhoan)
        {
            bool ketQua = true;
            try
            {
                base.Connect();

                string sqlString = "DELETE from NguoiDung WHERE TaiKhoan = '" + taikhoan + "'";

                ExecuteNonQuery(sqlString);
            }/*
            catch (Exception ex)
            {
                ketQua = false;
            }*/
            finally
            {
                if (connection != null && connection.State == System.Data.ConnectionState.Open)
                    Disconnect();
            }
            return ketQua;
        }

        public bool SuaNguoiDungTheoTaiKhoan(string matkhau, string maquyen, string hoten, string diachi, string email, string sodienthoai, string ghichu, string taikhoan)
        {
            bool ketQua = true;
            try
            {
                base.Connect();

                string sqlString = "UPDATE NguoiDung SET MatKhau = '" +
                    matkhau + "', MaQuyen= '" +
                    maquyen + "', HoTen='" +
                    hoten + "', DiaChi='" +
                    diachi + "', Email='" +
                    email + "', SoDienThoai='" +
                    sodienthoai + "', GhiChu='" +
                    ghichu + "' WHERE TaiKhoan = '" + taikhoan + "'";
                 
                ExecuteNonQuery(sqlString);
            }/*
            catch (Exception ex)
            {
                ketQua = false;
            }*/
            finally
            {
                if (connection != null && connection.State == System.Data.ConnectionState.Open)
                    Disconnect();
            }
            return ketQua;
        }

        
        protected override object GetDataRowFromDataReader(SqlDataReader dr)
        {
            NguoiDung_DAL nguoiDung = new NguoiDung_DAL();
            nguoiDung.Taikhoan = dr.GetString(0);
            if (!dr.IsDBNull(1))
                nguoiDung.Matkhau = dr.GetString(1);
            if (!dr.IsDBNull(2))
                nguoiDung.Maquyen = dr.GetString(2);
            if (!dr.IsDBNull(3))
                nguoiDung.Hoten = dr.GetString(3);
            if (!dr.IsDBNull(4))
                nguoiDung.Diachi = dr.GetString(4);
            if (!dr.IsDBNull(5))
                nguoiDung.Email = dr.GetString(5);
            if (!dr.IsDBNull(6))
                nguoiDung.Sodienthoai = dr.GetString(6);
            if (!dr.IsDBNull(7))
                nguoiDung.Ghichu = dr.GetString(7);

            return (object)nguoiDung;
        }
    }
}